Deconstruction Materials Weight Calculations

This tool consists of five R Markdown files which, when executed in consecutive order, can be used to determine and report environmental benefits of deconstruction over mechanical demolition of residential single family homes.

Purpose and Outputs

Part 1 converts quantities from material inventory reports into quantities that are compatible with the LCA impact factors used to develop net environmental benefits of residential deconstruction projects.

The outputs of this notebook are summarized in the following table:

Output Name	Description
`default_material_dimensions` table	table of the default weight and dimensions for materials where information was either missing or incomplete
`weight_calc` data table	complete record of intermediate variable steps used to convert quantities reported in “deconstruction_receipts_summary.xlsx” converted to “kg”. Useful for data QA/QC check
`missing_deconMaterialName.csv`	list of material items reported that do not have a material category to assign LCA impacts
`missing_quantity_data.csv`	contractor reported data with missing or incomplete information, where no default or assumed values could be used
`decon_material_weight` data table	cleaned data from the `weight_calc` table, which will be used to develop line item specific impacts in the successive R Markdown documents

Required Data Sources and File Locations

Sources of data for this analysis include:

Receipts from dropbox and recycling haulers;
Handwritten sale or donation slips;
Salvage retailer inventory forms and lists.

Typically, materials data from receipts and inventory sources must be collected, entered into a spreadsheet where each row is a separate material line item with column variables specified for R compatibility, and formatted into a csv file. An input file template is included with the R project file and the City of Portland 2017 deconstruction data set is also available as an example of a compatible input file. Users who wish to use their own file for analysis are encouraged to add a copy of the spreadsheet in csv format to the R project folder and replace the code references to the City of Portland data file with their file name as directed in the code sections that follow. It is recommended that users avoid coding file directory specifications into the R Markdown files. Additionally, caution is warranted that user’s existing global options may impact or override the R project file options.

It is important to note that R is a case sensitive language, so capitalization and spaces are common sources of incompatibility of input files with the tool scripts. For more information on utilizing R Markdown files visit https://rmarkdown.rstudio.com/authoring_quick_tour.html

New input files

When creating a new input file for analysis, starting with the template included is advised. Column names must match exactly if they are to be included, and several fields have specific entry options for proper data processing and handling. Figure 1 is an example of a properly formatted input file

FIGURE 1: Example input file format

The input file field deconMaterialName is a vector of character strings that are used to map the data observation quantity to the LCA impact factor data. This value is determined and assigned in the data entry phase and requires some attention to detail. See Appendix for the current list of options for completing this field and assigning material types to new data. Presently there is no automated process for this, users must decide and assign the material category that best fits the material line item. These material categories will be expanded to include more types of building materials in future tool iterations. Before the data summary table is cleaned, missing values will be summarized and handled according to what is not available, therefore it is ok to include data from a deconstruction project that doesn’t yet have an appropriate deconMaterialName that can be assigned.

Additionally, dimensions is a crucial user input field that contains the information needed to calculate material volumes that will be converted to weight. The format requires entry of the product actual dimensions (as distinguished from the name or nominal product dimensions) separated by a lower case “x”, with the associated units recorded in the dimensional_units column. This is one of the most important features of the data entry because the weight of framing and other lumber is calculated based on the lumber density and the calculated volume, which is determined by the physical dimensions of the materials. In future iterations of the tool, this process may be automated with a feature for optional manual entry.

For materials with subcategory names in the deconMaterialName variable, such as doors, windows, and cabinets, the description field should include just the main category material name as this is used for assigning default values during data processing. Other line item description entries should be short and contain enough information to determine the material type (deconMaterialName), dimensions not specified in the dimensions column such as plywood other sheet material thickness, wood species, or other details needed to convert from the quantity of the material reported in the receipt or inventory list to a weight value in kilograms. Additional detail about material line items should be entered in the notes column as this is kept only for documentation purposes and will only used for quality control.

Notebook Setup

In order to execute all five R Markdown files, users will need to have R and RStudio installed with the tidyverse packages in addition to the following packages available from CRAN: corrplot, ggrepel, ggthemes, knitr, rebus. More information about R, RStudio, and the packages used to build and deploy the deconstruction tool can be found here: https://www.rstudio.com/products/rpackages/

Each R Markdown file contains code chunks and narrative text which automates the code execution and document formatting. Code can be executed one chunk at a time, or the appropriate files names can be adapted in the code and the entire document rendered as an HTML (recommended for best formatting), pdf, or word document.

packages <- c("knitr", "rebus", "tidyverse")
lapply(packages, require, character.only = T)

## Loading required package: knitr

## Loading required package: rebus

## Loading required package: tidyverse

## -- Attaching packages ----------------------------------------------------------------------------------------------------------------------- tidyverse 1.2.1 --

## v ggplot2 3.0.0     v purrr   0.2.5
## v tibble  1.4.2     v dplyr   0.7.6
## v tidyr   0.8.1     v stringr 1.3.1
## v readr   1.1.1     v forcats 0.3.0

## -- Conflicts -------------------------------------------------------------------------------------------------------------------------- tidyverse_conflicts() --
## x ggplot2::alpha() masks rebus::alpha()
## x dplyr::filter()  masks stats::filter()
## x dplyr::lag()     masks stats::lag()
## x stringr::regex() masks rebus::regex()

## [[1]]
## [1] TRUE
## 
## [[2]]
## [1] TRUE
## 
## [[3]]
## [1] TRUE

options(digits = 3)
options(scipen = 999)

Data exploration begins with loading the csv files into the R Notebook and listing descriptions of the various features and fields in the data. Before diving into the data, a few reference tables will be generated, printed in an appendix, and saved for future use.

Conversion Factors and Default Values

Definitions and Abbreviations

First we need to define the abbreviations that may be used throughout the project documents. These are summarized in Appendix table A1

definitions <- tibble(
   abbreviations = c("bd_ft", "lin_ft","sq_in", "sq_ft", "cu_ft", "cu_m", "kg", "lbs","gal", "tons", "m_tons", "MJ", "kg_CO2e", "googUC", "awcEPD", "ahacAHG", "OSB (or osb)", "CVG", "RC", "DF", "T&G"), 
   unit_name = c("board feet", "lineal feet", "square inches", "square feet", "cubic feet", "cubic meters", "kilograms", "pounds", "US gallons", "US tons", "metric tons", "mega joules", "kilograms carbon dioxide equivalent", "Google Unit Converter tool", "American Wood Council environmental product declaration", "American Hardwood Export Council American Hardwood Guide", "oriented strand board wood product", "clear vertical grain wood", "rough cut lumber", "douglas fir product", "tongue-and-groove cut wood finish product")
   )

Unit Conversion Factors

Next a collection of unit conversions and their sources is given the unit conversions table (see appendix A2).

unit_conversions <- tibble(
   application = c("US area conversion", "US old growth lumber volume conversion", "US metric volume conversion", "metric softwood density", "metric hardwood density", "US metric weight conversion", "US short tons conversion", "US volume conversion"),
   value = c(144, 12, 35.3147, 530.70, 770, 2.2046, 907.185, 7.48052),
   value_unit = c("sq_in", "bd_ft", "cu_ft", "kg", "kg", "kg", "kg", "gal"),
   per = c("sq_ft", "cu_ft", "cu_m", "cu_m", "cu_m", "lbs", "US_ton", "cu_ft" ),
   info_source = c("googUC", "googUC", "googUC", "OSUtable2average", "ahacAHG", "googUC","googUC", "googUC")
)

# for convenience, add a couple more conversions that are derived from the values in the unit_conversions table defined above
# first add US volume (in cubic feet) conversions for the wood density factors (keeping in kilograms to match impact factor `declaredUnit`)
# the `dplyr::add_row()` function does not like to do calculations for assigning values to the fields, so initially the value is set to 0 then subsequent code lines do the math
unit_conversions <- unit_conversions %>%
   add_row(application = "US metric softwood density", 
           value = 0,
           value_unit = "kg",
           per = "cu_ft",
           info_source = "calculated"
   ) %>%
   add_row(application = "US metric hardwood density", 
           value = 0,
           value_unit = "kg",
           per = "cu_ft",
           info_source = "calculated"
   )
# US metric softwood density value = metric softwood density value (kg per cu_m) / US metric volume conversion (cu_ft per cu_m)
unit_conversions[9,2] <- unit_conversions[4,2]/unit_conversions[3,2]
# US metric hardwood density value = metric hardwood density value (kg per cu_m) / US metric volume conversion (cu_ft per cu_m)
unit_conversions[10,2] <- unit_conversions[5,2]/unit_conversions[3,2]

# create the value_reciprocal column
unit_conversions$value_reciprocal <- 1/unit_conversions$value

unit_conversions <- unit_conversions[,c(1:4,6,5)]

# named factors needed as numeric classification for use in later mutate call
softwood_density_factor <- as.numeric(unit_conversions[9,2])
hardwood_density_factor <- as.numeric(unit_conversions[10,2]) 
lbs_to_kg <- as.numeric(unit_conversions[6,5])
short_tons_conversion <- as.numeric(unit_conversions[7,2])

Default Material Dimensions

In some instances, the commonly reported values and units for material items is inadequate for determining the weight of the item. This typically occurs with fixtures such as doors, toilets, and lighting. Where the weight of these items is known or can be measured, that actual value should be used, however, when this is impractical, the following table of default material dimensions will be used to substitute values during the data cleaning routines at the end of this document.

The default material dimensions match line item descriptions to factors which can then be multiplied by the listed_quantity to get the amount of the line item material that will be converted to declaredUnits and used to generate impact values.

# first generate a data frame from a list of the materials which will be built out 
default_material_dimensions <- tibble(
   description = c("tub", "sink",
                  "tub", "sink", "angle iron", 
                  "toilet","sink", "tile (box)", 
                  "sink", "screen door", "garage door", "security door", "window bars", "metal post", "hardware", "railing", "vent", "gutter", "grab bar", "tub", "corrugated sheetmetal",
                  "carpet",
                  "plywood",
                  "osb",
                  "lath", "flooring (sq_ft)", "porch corbels", "newel post", "banister", "column", "mantel",
                  "flooring (sq_ft)", "bench", "grate",
                  "flooring (sq_ft)", "steps",
                  rep("doors", 3),
                  rep("windows", 3),
                  rep("cabinets", 3),
                  "light fixtures"
                  ),
   deconMaterialName = c(rep("fiberglass tub", 2),
                        rep("cast iron", 3), 
                        rep("ceramics", 3), 
                        rep("steel product", 13),
                        "carpeting",
                        "plywood",
                        "osb",
                        rep("softwood lumber", 7),
                        rep("hardwood lumber", 3),
                        rep("hardwood flooring", 2),
                        "outer door (solid wood)", "inner door (solid wood)", "inner door (hollow wood)",
                        "window (single wood)", "window (double wood)", "window (double vinyl)",
                        "cabinets (lower)", "cabinets (upper short)", "cabinets (upper long)",
                        "light fixture"),
   default_value = c(60, 10, 
                     300, 50, 222.85,
                     100, 20, 50,
                     10, 15, 250, 100, 50, 50, 5, 25, 5, 5, 5, 75, 1,
                     2,
                     3,
                     3.4,
                     18.4, 1/12, 5, 10, 20, 50, 100,
                     1/12, 100, 5,
                     1/12, 1/12*36*12/144, 
                     46.83, 23.26, 18.29,
                     1.6265*12, 2.1897*12, 2.4471*12,
                     15.27, 8.21, 11.08,
                     5
   ),
   default_value_units = c(rep("lbs", 2),
                          rep("lbs", 2), "kg per cu_ft",
                          rep("lbs", 3),
                          rep("lbs", 12), "lbs per sq_ft",
                          "lbs per sq_ft",
                          "lbs per sq_ft per inch thickness",
                          "lbs per sq_ft per inch thickness",
                          "lbs per bundle", "ft thickness", rep("lbs", 5),
                          "ft thickness", rep("lbs", 2),
                          "ft thickness", "cu_ft per 12x36x1_inch step",
                          rep("kg per door", 3),
                          rep("kg per (3'x4' = 12sq_ft) window", 3),
                          rep("kg per lineal ft", 3),
                          "lbs per fixture"
   )
)

write_csv(default_material_dimensions, "output/default_material_dimensions.csv")

Data conversion routines

Before beginning the data cleaning and preparation, the spreadsheet containing the raw data summary must be loaded into the R session. This is point where new users can substitute their own csv file for the public_use_raw_data_summary.csv file that is used for the City of Portland report. Simply add the new file to the R project file then in the call to read_csv() replace the file name inside the quotes.

# import raw data summary file
raw_data_summary_sheet <- read_csv("data/public_use_raw_data_summary.csv")

## Parsed with column specification:
## cols(
##   project = col_integer(),
##   contractor = col_character(),
##   house_age = col_integer(),
##   house_size = col_integer(),
##   deconMaterialName = col_character(),
##   listed_quantity = col_double(),
##   listed_units = col_character(),
##   dimensions = col_character(),
##   dimensional_units = col_character(),
##   description = col_character(),
##   notes = col_character()
## )

# to read in new data, drop the new csv file into the project data folder 
# add a hashtag to comment out the import command above 
# substitute the file path/name in the command below
# then remove the hashtag to uncomment the import command with your new file name
# 
# import new data file
# raw_data_summary_sheet <- read_csv("data/replace with your file name here.csv")

The following steps are used to convert from commonly used building material quantity reporting units to calculated weight values in declaredUnits that can be multiplied by impact factors for generating the net benefits calculations:

Step One: Dimensional units conversion

# create rebus objects for character string parsing
separators <- char_class("./ ")

sq_dimensions <- one_or_more(DGT) %R% optional(one_or_more(DGT)) %R% optional(separators) %R% optional(one_or_more(DGT)) %R% optional(one_or_more(DGT)) %R% "x" %R% one_or_more(DGT) %R% optional(one_or_more(DGT)) %R% optional(separators) %R% optional(one_or_more(DGT)) %R% optional(one_or_more(DGT))

# extract dimensions
dimension_calc_extract <- str_extract_all(raw_data_summary_sheet$dimensions, sq_dimensions, simplify = T) %>%
   str_split("x", n = 2, simplify = T)

# create a new data frame using the extraction object
dimension_product <- data.frame(dimension_calc_extract[1:length(raw_data_summary_sheet$dimensions),1:2], stringsAsFactors = F)

# convert vector class and add units
dimension_product[,1] <- as.numeric(dimension_product[,1])
dimension_product[,2] <- as.numeric(dimension_product[,2])
dimension_product$dim_units <- raw_data_summary_sheet$dimensional_units

# multiply dimensions and convert units where necessary
dimension_product <- dimension_product %>%
   mutate(sq_dim = dimension_product[,1] * dimension_product[,2]) %>%
   mutate(sq_dim_units = case_when(
      dim_units == "in" ~ "sq_in",
      dim_units == "ft" ~ "sq_ft")
   ) %>%
   mutate(sq_ft_dim = case_when(
      sq_dim_units == "sq_in" ~ sq_dim/144,
      sq_dim_units == "sq_ft" ~ sq_dim)
   )

# clean up workspace
rm(dimension_calc_extract)
rm(sq_dimensions)
# don't delete dimension_product because it is needed to calculate plywood and osb weight

Step Two: Calculate material units from dimensions

Create the per_unit_value which represents calculated value from dimensions given for the line item description:

This new per_unit_value is the line item quantity in units compatible with the listed_quantity values reported by the contractors, and is dependent upon the type of material. To illustrate, the per_unit_value for Softwood lumber is the dimensional expression of lumber which can be multiplied by the listed_quantity reported by contractors to achieve a consistent cubic foot volume measure. For engineered wood products, contractors reported the number of sheets of plywood or oriented strand board and in some cases, included the sheet dimensions (here thickness is relevant, but default values can be used where data is missing). The per_unit_value for these items is the result of calculating the weight per sheet according to either reported or assumed product dimensions.

The default_value represents the assumed conversion from the listed_units to weight, with the caveat that many values will need an additional conversion before matching the impact_units (which is typically in kilograms).

# this line preserves the raw data summary and creates an object with column order rearranged to facilitate creating an object whose variable transformations can be read from left to right columnwise    
material_dimensions_calc <- raw_data_summary_sheet %>%
   select(c( "project", "contractor", "house_age", "house_size", "description", "notes", "deconMaterialName", "listed_quantity", "listed_units", "dimensions", "dimensional_units"))

# match up the converted square footage of materials to the raw_data_summary_sheet table; this takes care of most of the lumber line items

material_dimensions_calc <- material_dimensions_calc %>%
   mutate(per_unit_value = case_when(
   .$listed_units == "bd_ft" ~ 1/12,
   .$listed_units == "sq_ft" ~ dimension_product$X1/12,
   .$listed_units == "lin_ft" ~ dimension_product$sq_ft_dim,
   .$description == "cabinets" & .$listed_units == "count" ~ 3,
   .$deconMaterialName == "plywood" ~ dimension_product$sq_dim,
   .$deconMaterialName == "osb" ~ dimension_product$sq_dim,
   .$deconMaterialName == "carpeting" ~ 100,
   .$description == "corrugated sheetmetal" ~ dimension_product$sq_ft_dim,
   .$description == "angle iron" ~  161.05)
   ) %>% 
   mutate(per_unit_value_units = case_when(
   .$listed_units == "bd_ft" ~ "cu_ft_conversion",
   .$listed_units == "sq_ft" ~ "ft thickness",
   .$description == "cabinets" & .$listed_units == "count" ~ "lin_ft",
   .$listed_units == "lin_ft" ~ "sq_ft",
   .$deconMaterialName == "plywood" ~ "sq_ft",
   .$deconMaterialName == "osb" ~ "sq_ft",
   .$deconMaterialName == "carpeting" ~ "assumed_sq_ft_per_roll",
   .$description == "corrugated sheetmetal" ~ "sq_ft",
   .$description == "angle iron" ~ "kg")
   )

# weight calculations table
weight_calc <- left_join(material_dimensions_calc, default_material_dimensions,  by = c("description", "deconMaterialName"))

# clean up workspace, but don't remove dimension product just yet, as it will be needed later to calculate plywood & osb sheet weights
rm(material_dimensions_calc)

Engineered wood products For engineered wood products (Plywood and OSB) the default thickness is 1/2 inch, and default sheet size is 32 square feet (4’ by 8’). The default material dimension value is lbs per sq_ft per inch thickness (which is an equivalent board feet measure), so the per_unit_value calculation therefore represents thickness as a percentage of an inch. An example of the calculation for 1 sheet of OSB (4’ by 8’) at 1/2" thickness would be:

\[ 0.5in * 32 sq ft * 2.75 \frac {lbs}{sqft*in} = 44lbs\]

More work is needed here to convert per_unit_value of the plywood and OSB items from theper_unit_value_unit in square feet that resulted from multiplying the dimensions to the weight per sheet so that multiplying the contractor reported values in sheets of engineered wood product by the per_unit_value will produce a calculated_quantity that is the total weight of the sheets of wood product. This code block generates a table which collects the reported_thickness and default_thickness in inches, the sq_ft_dim product dimension in square feet for the line item, and the default_value of pounds per square feet per inch of thickness which in turn are used to calculate the per_unit_value weight per sheet for that specific engineered wood product line item.

# which items reported the thickness?
finding_engineered_wood_thickness <- DGT %R% "/" %R% DGT #%R% '" ' 

thickness_calc_extract <- str_extract_all(raw_data_summary_sheet$description, 
                                          finding_engineered_wood_thickness, simplify = T) %>%
                           str_split(separators, n = 2, simplify = T)

thickness <- data.frame(thickness_calc_extract[1:length(raw_data_summary_sheet$description),1:2], stringsAsFactors = F)

thickness <- tibble(as.numeric(thickness[,1]) / as.numeric(thickness[,2]))

thickness$deconMaterialName <- weight_calc$deconMaterialName


# assign the default thickness to the rest of the items where thickness was not reported
thickness <- thickness %>% mutate(default_thickness = case_when(
                              .$deconMaterialName == "osb" ~ 0.5,
                              .$deconMaterialName == "plywood" ~ 0.5)) %>%
                           left_join(default_material_dimensions, by = c("deconMaterialName" = "description")) %>%
                           add_column(dimension_product$sq_ft_dim) %>%
                           select(c(2, 1, 3, 5:7))

names(thickness) <- (c("deconMaterialName", "reported_thickness", "default_thickness", "default_weight", "default_weight_units", "sq_ft_dim"))

# use the equation above to produce a new per_unit_value that gives the weight of a sheet of that product line item.
thickness <- thickness %>% mutate(sheet_weight = ifelse(is.na(thickness$reported_thickness),
                                                      default_thickness * default_weight * sq_ft_dim,
                                                      reported_thickness * default_weight * sq_ft_dim
                           ))

weight_calc <- weight_calc %>% add_column(thickness$sheet_weight, .before = "per_unit_value") %>%
                              rename(sheet_weight = `thickness$sheet_weight`)

weight_calc <- weight_calc %>% mutate(per_unit_value = ifelse(is.na(sheet_weight),
                                                             per_unit_value,
                                                             sheet_weight)
                                     ) %>%
                              mutate(per_unit_value_units = ifelse(is.na(sheet_weight),
                                                             per_unit_value_units,
                                                             "lbs per sheet")
                                     ) #%>%
                              #select(-sheet_weight)

rm(separators)
rm(dimension_product)
rm(finding_engineered_wood_thickness)
rm(thickness_calc_extract)
rm(thickness)

Step Three: Quantity calculations

Create calculated_quantity by multiplying the listed_quantity by either the per_unit_value or the default_value:

With regard to coding and automating, it will be easier to convert most quantities to a consistent calculated value and then do the final conversion to kilograms.

weight_calc <- weight_calc %>% 
   mutate(calculated_quantity = case_when(
      .$deconMaterialName == "dropbox" ~ .$listed_quantity,
      .$default_value_units == "lbs per sq_ft" ~ .$listed_quantity * .$per_unit_value * .$default_value,
      .$description == "cabinets" & .$listed_units == "count" ~ .$listed_quantity * .$per_unit_value * .$default_value,
      .$description == "cabinets" & .$listed_units == "lin_ft" ~ .$listed_quantity * .$default_value,
      .$deconMaterialName == "carpeting" ~ .$listed_quantity * .$per_unit_value * .$default_value,
      .$description == "windows" ~ .$listed_quantity * .$default_value,
      .$description == "doors" ~ .$listed_quantity * .$default_value,
      .$per_unit_value_units == "lbs per sheet" ~ .$listed_quantity * .$per_unit_value,
      .$default_value > 0 ~ .$listed_quantity * .$default_value,
      .$per_unit_value > 0 ~ .$listed_quantity * .$per_unit_value,
      .$listed_units == "lbs" ~ .$listed_quantity)
   ) %>%
   mutate(calculated_quantity_units = case_when(
      .$deconMaterialName == "dropbox" ~ .$listed_units,
      .$default_value_units == "lbs per sq_ft" ~ "lbs",
      .$deconMaterialName == "steel product" & .$per_unit_value > 0 ~ .$per_unit_value_units,
      .$deconMaterialName == "steel product" & .$default_value > 0 ~ .$default_value_units,
      .$description == "cabinets" ~ "kg",
      .$description == "doors" ~ "kg",
      .$description == "windows" ~ "kg",
      .$deconMaterialName == "carpeting" ~ "lbs",
      .$per_unit_value_units == "lbs per sheet" ~ "lbs", 
      .$default_value_units == "lbs per bundle" ~ "lbs",
      .$default_value_units == "lbs per fixture" ~ "lbs",
      .$per_unit_value_units == "sq_ft" & .$default_value_units == "lbs per sq_ft" ~ "lbs",
      .$listed_units == "bd_ft" ~ "cu_ft",
      .$per_unit_value_units == "sq_ft" & .$listed_units == "lin_ft" ~ "cu_ft",
      .$per_unit_value_units == "ft thickness" & .$listed_units == "sq_ft" ~ "cu_ft",
      .$deconMaterialName == "hardwood flooring" ~ "cu_ft",
      .$default_value_units == "lbs" ~ .$default_value_units,
      .$listed_units == "lbs" ~ .$listed_units)
   )

# clean up calculated units
calc_unit_cleanup <- str_split_fixed(weight_calc$calculated_quantity_units, "per", n = 3)
weight_calc$calculated_quantity_units <- calc_unit_cleanup[,1]
rm(calc_unit_cleanup)

Step Four: Final conversion to metric weight units

Assign metric conversion factors to quantity information for the final calculation

For this next step, we need to convert material volume (mostly wood products) to weight using material density factors, and convert English weight units to metric. This is accomplished by assigning values from the unit_conversions table based on the calculated_quantity_units in the weight_calc table.

weight_calc <- weight_calc %>% 
   mutate(converted_quantity = case_when(
      .$calculated_quantity_units == "cu_ft" & .$deconMaterialName == "hardwood flooring" ~ .$calculated_quantity * hardwood_density_factor,
      .$calculated_quantity_units == "cu_ft" & .$deconMaterialName == "softwood lumber" ~ .$calculated_quantity * softwood_density_factor,
      .$calculated_quantity_units == "kg" ~ .$calculated_quantity,
      .$calculated_quantity_units == "lbs" ~ .$calculated_quantity * lbs_to_kg,
      .$calculated_quantity_units == "tons" ~ .$calculated_quantity * short_tons_conversion
   )
   ) %>% 
   mutate(converted_quantity_units = ifelse(is.na(.$converted_quantity), NA, "kg"))

# save for calculation verification, all variables and observations included
write.csv(weight_calc, "intermediary/weight_calc.csv")

Final preparations

Document missing/incomplete data

The strategy for handling missing data is dependent upon the source of the missing data. Some missing data have assumption based interpretations, which are documented in the assumptions list. Some incomplete data will have default values substituted such as those carried out in Step Two above. Where possible, average values can be used to impute missing information, but otherwise, missing data in the listed_quantity or listed_units field will result in the line item being omitted. Missing description values are of no consequence, materials with missing descriptions or notes will be included as this field is not an input for the quantification steps. In the following code chunks, the decon_material_weight table is checked for missing information. Then these missing data are collected and saved in separate csv files to assist in future tool development and for improving and informing data collection practices. Finally, based on the type of data missing, a determination is made on how the data will be handled and the cleaned and tidy data table of all materials from the deconstruction projects is saved for use in the next R Markdown file.

The following list gives the frequency of missing data by column name

countNA <- function(x) { sum(is.na(x)) }
map(select(weight_calc, c("deconMaterialName", "listed_quantity", "listed_units","converted_quantity", "converted_quantity_units") ) , countNA)

## $deconMaterialName
## [1] 57
## 
## $listed_quantity
## [1] 50
## 
## $listed_units
## [1] 48
## 
## $converted_quantity
## [1] 96
## 
## $converted_quantity_units
## [1] 96

Because deconMaterialName values are entered by the human reading the data from reciepts, missing values happen for a couple different reasons: * material types not represented in the LCA impact categories data (item is not on the list in the appendix) * receipt or inventory source does not contain enough information to determine and assign a deconMaterialName from the list Both reasons provide the analyst with different kinds of information: in the first case, this is an opportunity to share information and provide input to DEQ for improving and expanding the material options used in this tool; in the second case, it reinforces information about contractor data collection processes.

Missing deconMaterialName values represent line items that presently are material types not represented in the LCA impact categories data. Save missing deconMaterialName and optionally email the file to DEQ¹ to explore adding these materials to the list of LCA material impact factors.

missing_material_category <- filter(weight_calc, is.na(deconMaterialName)) %>%
   filter(!is.na(listed_quantity) )
write_csv(missing_material_category, "output/missing_deconMaterialName.csv", na = " ")

Missing data under listed_quantity or listed_units represents line items reported by contractors with no detail as to the quantity or other information needed to determine the impacts. Save missing quantity line items to help inform better data collection efforts.

missing_quantities <- filter(weight_calc, is.na(converted_quantity))
write_csv(missing_quantities, "output/missing_quantity_data.csv", na = " ")

In the City of Portland data, there are a couple problematic projects. There is a note in the raw receipts file for project number 26 that at least one of the dropbox hauler receipts is missing. Additionally, all quantity data is entirely lacking for salvaged materials on project number 28 (only quantity information given was from two dropbox receipts). Note contractor number 6 was the responsible party on both of these projects. This information is helpful for stakeholders, but for now requires that these projects be dropped from the data set. Another trend worth noting after removing projects number 26 and 28 from the missing_quantities data is that 36 of the remaining 42 items with missing data come from contractor 1. This could indicate a need for data collection training for these contractors.

A cleaned data table will be saved for use in the next analysis document.

decon_material_weight <- weight_calc %>%
   select( "project", "contractor", "house_age", "house_size", "description", "deconMaterialName", "listed_quantity", "listed_units", "dimensions", "dimensional_units", "converted_quantity", "converted_quantity_units") %>%
   filter(.$project != "26") %>%
   filter(.$project != "28") %>%
   drop_na(converted_quantity)
   
write.csv(decon_material_weight, "intermediary/decon_material_weight.csv")

Next Steps

The next step is to prepare the data set that will assign impact factors for the materials. The document 02_decon_data_prep2018.Rmd contains routines that use the decon_material_weight table and other data sources to produce a full table of materials with weights and impacts for the deconstruction scenario.

Appendix 01

Table A1 Definitions

kable(definitions)

abbreviations	unit_name
bd_ft	board feet
lin_ft	lineal feet
sq_in	square inches
sq_ft	square feet
cu_ft	cubic feet
cu_m	cubic meters
kg	kilograms
lbs	pounds
gal	US gallons
tons	US tons
m_tons	metric tons
MJ	mega joules
kg_CO2e	kilograms carbon dioxide equivalent
googUC	Google Unit Converter tool
awcEPD	American Wood Council environmental product declaration
ahacAHG	American Hardwood Export Council American Hardwood Guide
OSB (or osb)	oriented strand board wood product
CVG	clear vertical grain wood
RC	rough cut lumber
DF	douglas fir product
T&G	tongue-and-groove cut wood finish product

Table A2 Unit Conversions

kable(unit_conversions)

application	value	value_unit	per	value_reciprocal	info_source
US area conversion	144.00	sq_in	sq_ft	0.007	googUC
US old growth lumber volume conversion	12.00	bd_ft	cu_ft	0.083	googUC
US metric volume conversion	35.31	cu_ft	cu_m	0.028	googUC
metric softwood density	530.70	kg	cu_m	0.002	OSUtable2average
metric hardwood density	770.00	kg	cu_m	0.001	ahacAHG
US metric weight conversion	2.21	kg	lbs	0.454	googUC
US short tons conversion	907.18	kg	US_ton	0.001	googUC
US volume conversion	7.48	gal	cu_ft	0.134	googUC
US metric softwood density	15.03	kg	cu_ft	0.067	calculated
US metric hardwood density	21.80	kg	cu_ft	0.046	calculated

The way to read the table is for each application, starting with the numeric value, move from left to right using the column names between reading the row values. For example: lumber_USvolume_conversion has 12 board feet per cubic foot² according to the Google Unit Converter tool. Another example: metric_softwood_density has 433.57 kilograms per cubic meter according to the American Wood Council environmental product declaration. To convert from the per column unit back to the value_unit one must use the reciprocal of the value which is given by the value_reciprocal column in per units. These reciprocal values are then read in reverse from right to left. For example: according to the Google Unit Converter tool there is 0.083 (or 1/12) cubic feet in a board foot. Another example using the value_reciprocal column would be to read 2.20459 kilograms in the value_unit pound.

Table A3 Default Material Dimensions Table

saved in project file as “default_material_dimensions.csv”

kable(default_material_dimensions)

description	deconMaterialName	default_value	default_value_units
tub	fiberglass tub	60.000	lbs
sink	fiberglass tub	10.000	lbs
tub	cast iron	300.000	lbs
sink	cast iron	50.000	lbs
angle iron	cast iron	222.850	kg per cu_ft
toilet	ceramics	100.000	lbs
sink	ceramics	20.000	lbs
tile (box)	ceramics	50.000	lbs
sink	steel product	10.000	lbs
screen door	steel product	15.000	lbs
garage door	steel product	250.000	lbs
security door	steel product	100.000	lbs
window bars	steel product	50.000	lbs
metal post	steel product	50.000	lbs
hardware	steel product	5.000	lbs
railing	steel product	25.000	lbs
vent	steel product	5.000	lbs
gutter	steel product	5.000	lbs
grab bar	steel product	5.000	lbs
tub	steel product	75.000	lbs
corrugated sheetmetal	steel product	1.000	lbs per sq_ft
carpet	carpeting	2.000	lbs per sq_ft
plywood	plywood	3.000	lbs per sq_ft per inch thickness
osb	osb	3.400	lbs per sq_ft per inch thickness
lath	softwood lumber	18.400	lbs per bundle
flooring (sq_ft)	softwood lumber	0.083	ft thickness
porch corbels	softwood lumber	5.000	lbs
newel post	softwood lumber	10.000	lbs
banister	softwood lumber	20.000	lbs
column	softwood lumber	50.000	lbs
mantel	softwood lumber	100.000	lbs
flooring (sq_ft)	hardwood lumber	0.083	ft thickness
bench	hardwood lumber	100.000	lbs
grate	hardwood lumber	5.000	lbs
flooring (sq_ft)	hardwood flooring	0.083	ft thickness
steps	hardwood flooring	0.250	cu_ft per 12x36x1_inch step
doors	outer door (solid wood)	46.830	kg per door
doors	inner door (solid wood)	23.260	kg per door
doors	inner door (hollow wood)	18.290	kg per door
windows	window (single wood)	19.518	kg per (3’x4’ = 12sq_ft) window
windows	window (double wood)	26.276	kg per (3’x4’ = 12sq_ft) window
windows	window (double vinyl)	29.365	kg per (3’x4’ = 12sq_ft) window
cabinets	cabinets (lower)	15.270	kg per lineal ft
cabinets	cabinets (upper short)	8.210	kg per lineal ft
cabinets	cabinets (upper long)	11.080	kg per lineal ft
light fixtures	light fixture	5.000	lbs per fixture

Send inquiries and requests for new materials info to Palmeri.Jordan@deq.state.or.us↩
During the data entry process, special care and attention was given to ensuring that nominal lumber was reported as actual dimensions. All nominal lumber was reported in lineal feet, and is therefore converted to cubic feet volume. All lumber reported in board feet was of the old-growth dimensional type, so correction between nominal dimensions and actual dimensions for board feet is not needed.↩

Part 1 Deconstruction Wood Sensitivity Analysis

Material Weight Conversion using average density from OSU Table 2

Andey Nunes, MS

Research Analyst 2

Oregon DEQ

October 22, 2018